﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class Total_Statistic : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string sql_script =
@"
SELECT temp.part_id AS part_id,
(SELECT p.title FROM [Parts] AS p WHERE p.id = temp.part_id) AS part_title,
MIN(temp.total_mark) AS min_mark, 
MAX(temp.total_mark) AS max_mark, 
AVG(temp.total_mark) AS avg_mark
FROM
(SELECT q.part_id AS part_id, SUM(a.mark) AS total_mark
FROM
[Answers] AS a 
JOIN [Questions] AS q
ON a.question_id = q.id
JOIN [Participants] AS pant
ON a.participant_id = pant.id
GROUP BY q.part_id, pant.id) AS temp
GROUP BY temp.part_id
";
        IDbConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SurveyConnectionString"].ConnectionString);
        using (conn)
        {
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql_script;            
            IDataReader dr = cmd.ExecuteReader();

            GridView1.DataSource = dr;
            GridView1.DataBind();
            dr.Close();
        }
    }
}
